#!/use/bin/python
# coding=utf-8
# 将 生活助手版本分布 输出到飞书
import redis
import datetime
import pymysql
import requests
import json
import pandas as pd
from tabulate import tabulate

from dbutils.pooled_db import PooledDB


logDetail = []

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


def printFeiShu(msg):
    msg_content = "{env}\n\n{msg}".format(env=env, msg="".join(msg))
    print(msg_content)

    payload = {
        "msg_type": "text",
        "content": {
            "text": msg_content
        }
    }

    headers = {
        'Content-Type': 'application/json'
    }

    data = json.dumps(payload)

    # 按照utf-8编码成字节码
    data = data.encode("utf-8")
    #测试机器人
    #url = 'https://open.feishu.cn/open-apis/bot/v2/hook/4238d051-3d9a-438f-9614-eb324f2ea845'
    #正式机器人
    url = "https://open.feishu.cn/open-apis/bot/v2/hook/4b102c84-338a-4bef-87f9-441ecd3f737b"
    response = requests.post(url, data=data, headers=headers)
    print(response.text)


def getAppVersion():
    conn, cur = getDataConnection()
    selectSql = "select appVerName '版本',count(1) '数量' from device_analysis t group by t.appVerName order by t.count(1) desc";
    df = pd.read_sql(selectSql, con=conn)
    msg = tabulate(df, headers='keys', tablefmt='pretty')
    logDetail.append(msg)

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

if __name__ == '__main__':
    d =1
    #today = todayYMD()
    toDay = getdate(d)
    env = f"{toDay}日期,生活助手版本分布"
    print(env)
    getAppVersion()
    printFeiShu(logDetail)
